﻿-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[proc_LoginJL_GetList]
(
	@CompanyId int,
	@isLocal VARCHAR(1),
	@IP VARCHAR(50),
	@loginname nvarchar(50),
	@StartDate varchar(20),
	@EndDate varchar(20),
	@StartIndex int,
	@EndIndex int
)
as
Begin
Declare @cSql varchar(2000)

Set @cSql = 'L.userCompanyId=' + Convert(varchar(10),@CompanyId)

If @loginname<>''
	Set @cSql = @cSql + ' And L.loginname Like ''%' + @loginname + '%'''

If @IP<>''
	Set @cSql = @cSql + ' And L.IP Like ''%' + @IP + '%'''
Else	--ip为空
	Begin
		If @isLocal = '1'
			Set @cSql = @cSql + ' And L.IP<>''60.223.227.187'' And L.IP<>''218.24.166.12'' And L.IP<>''222.171.225.189'' And Left(L.IP,8)<>''192.168.'''
		Else
			Set @cSql = @cSql + ' And (L.IP=''60.223.227.187'' Or L.IP=''218.24.166.12'' Or L.IP=''222.171.225.189'' Or Left(L.IP,8)=''192.168.'')'
	End

Set @cSql = @cSql + ' And L.logindate>=@sRq And L.logindate<=@eRq'

Set @cSql='Declare @sRq Datetime,@eRq Datetime
Set @sRq = Convert(Datetime,''' + @StartDate + ' 00:00:00'')
Set @eRq = Convert(Datetime,''' + @EndDate + ' 23:59:59'')

	;WITH list As(Select ROW_NUMBER() OVER (Order By id DESC)AS Row,
		L.id,
		L.loginname,
		L.loginuserid,
		L.userCompanyId,
		L.userComapnyName,
		L.logindate,
		L.IP,
		ISNULL(L.memo,'''') AS memo
	From LoginJL L
	Where '+@cSql + '
	)
	
	Select *,(Select Count(0) From list) As cid From list Where Row Between '+Convert(varchar(10),@StartIndex)+' and '+Convert(varchar(10),@EndIndex)+' Order By Row'		

Exec(@cSql)
--Select @cSql

--	BEGIN
--DECLARE @StartTime DATETIME
--DECLARE @EndTime DATETIME
--SET @StartTime = @StartDate + ' 00:00:00'
--SET @EndTime = @EndDate + ' 23:59:59'
--		;WITH list As(Select ROW_NUMBER() OVER (Order By id DESC)AS Row,
--			L.id,
--			L.loginname,
--			L.loginuserid,
--			L.userCompanyId,
--			L.userComapnyName,
--			L.logindate,
--			L.IP,
--			ISNULL(L.memo,'') AS memo
--			From LoginJL L
--			Where (@CompanyId = 0 Or L.userCompanyId = @CompanyId)
--			AND (L.logindate >= @StartTime AND L.logindate <= @EndTime)
--			And ((@IP = '' AND @loginname != ''  AND L.loginname LIKE '%' + @loginname + '%') 
--			Or (@loginname = '' AND @IP != '' AND L.IP LIKE '%' + @IP + '%') 
--			Or (@loginname = '' AND @IP = ''))
--			And
--		)
--		
--		Select *,(SELECT Count(1) From list) AS cid From list
--		Where Row between @StartIndex and @EndIndex	
--	End
End
